/**
 * 
 */
package com.offlinerssreader.model;

import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.jws.WebService;

import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException; 
import org.json.JSONException;
import org.json.JSONObject;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * @author Elad
 *
 */
public class RssFeed extends GenericModel {
	private int id;
	public String name;
	public String url;
	public int user;
	
	/*
	 * CREATE  TABLE `rssFeed` (
	 * `id` INT NOT NULL AUTO_INCREMENT ,
	 * `name` VARCHAR(45) NOT NULL ,
	 * `url` VARCHAR(200) NOT NULL ,
	 * `user` INT NOT NULL ,
	 * PRIMARY KEY (`id`) );
	 */
	
	public static final String TABLE_NAME = "rssFeeds";
	public static final String COL_NAME_ID = "id";
	public static final String COL_NAME_NAME = "name";
	public static final String COL_NAME_URL = "url";
	public static final String COL_NAME_USER = "user";
	
	
	public RssFeed() {
		super();
		this.id = 0;
	}
	
	/**
	 * 
	 * @param id
	 * @throws Exception
	 */
	public RssFeed(int id) throws Exception {
		super();
		load(id);
	}
	
	public RssFeed(String name, String url, int user) {
		this.name = name;
		this.url = url;
		this.user = user;
	}

	/* (non-Javadoc)
	 * @see com.offlinerssreader.model.GenericModel#save()
	 */
	@Override
	public boolean save() throws Exception {
		String query;
		Statement statement;
		if (this.id == 0)
			throw new Exception("Trying to delete without id");
		
		query = "UPDATE " +  RssFeed.TABLE_NAME + " SET VALUES " +
				RssFeed.COL_NAME_NAME + "'" + this.name + "', " +
				RssFeed.COL_NAME_URL + "'" + this.url + "', " +
				" WHERE " + RssFeed.COL_NAME_ID + " = '" + this.id + "'";
		
		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		return true;
	}

	/* (non-Javadoc)
	 * @see com.offlinerssreader.model.GenericModel#delete()
	 */
	@Override
	public boolean delete() throws Exception {
		String query;
		Statement statement;
		LinkedList<RssItem> items;
		Iterator<RssItem> itr;
		RssItem itm;
		
		if (this.id == 0)
			throw new Exception("Trying to delete without id");
		
		items = getAllItems(true);
		itr = items.iterator();
		while (itr.hasNext()) {
			itm = itr.next();
			itm.delete();
		}
		
		items = getAllItems(false);
		itr = items.iterator();
		while (itr.hasNext()) {
			itm = itr.next();
			itm.delete();
		}

		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}
		query = "DELETE FROM " +  RssFeed.TABLE_NAME + " WHERE " + RssFeed.COL_NAME_ID + " = '" + this.id + "'";
		
		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		return true;
	}

	/* (non-Javadoc)
	 * @see com.offlinerssreader.model.GenericModel#load(java.lang.Object)
	 */
	@Override
	public boolean load(Object key) throws Exception {
		this.id = (Integer)key;
		String query;
		Statement statement;
		ResultSet result;
		
		if (this.id == 0)
			throw new Exception("Trying to delete without id");
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}

		query = "SELECT * FROM " +  RssFeed.TABLE_NAME + " WHERE " + RssFeed.COL_NAME_ID + " = '" + id + "'";
		
		statement = this.conn.createStatement();
		result = statement.executeQuery(query);
		if (!result.next()) {
			result.close();
			return false;
		}
		this.name = result.getString(RssFeed.COL_NAME_NAME);
		this.url = result.getString(RssFeed.COL_NAME_URL);
		this.user = result.getInt(RssFeed.COL_NAME_USER);
		result.close();
		return true;
	}

	@Override
	public boolean create() throws Exception {
		String query;
		Statement statement;
		ResultSet result;
		
		query = "INSERT INTO " +  RssFeed.TABLE_NAME + " ("
				+ "`" + RssFeed.COL_NAME_NAME + "`, "
				+ "`" + RssFeed.COL_NAME_URL + "`, "
				+ "`" + RssFeed.COL_NAME_USER + "`"
				+ ") VALUES ("
				+ "'" + this.name + "', "
				+ "'" + this.url + "', "
				+ "'" + this.user + "')";
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}
		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		
		query = "SELECT " + RssFeed.COL_NAME_ID
				+ " FROM " + RssFeed.TABLE_NAME
				+ " WHERE `" + RssFeed.COL_NAME_NAME + "` = '" + this.name + "' "
				+ "AND `" + RssFeed.COL_NAME_USER + "` = '" + this.user + "'";
		result = statement.executeQuery(query);
		result.next();
		this.id = result.getInt(RssItem.COL_NAME_ID);
		
		return true;
	}
	
	public static String getCreateTable() {
		return "CREATE TABLE IF NOT EXISTS `" + TABLE_NAME + "` ("
				+ "`" + COL_NAME_ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + COL_NAME_NAME + "` VARCHAR(45) NOT NULL ,"
				+ "`" + COL_NAME_URL + "` VARCHAR(200) NOT NULL ,"
				+ "`" + COL_NAME_USER + "` INT NOT NULL ,"
				+ "PRIMARY KEY (`" + COL_NAME_ID + "`) );";
	}
	
	public LinkedList<RssItem> getAllItems(boolean cached) throws Exception {
		LinkedList<RssItem> list = new LinkedList<RssItem>();
		String query;
		Statement statement;
		ResultSet result;
		RssItem item;
		int itemId;
		
		if (this.id == 0) {
			throw new Exception("Cannot get items without id");
		}
		
		if (cached) {
			query  = "SELECT " + RssItem.COL_NAME_ID + " FROM `" + RssItem.TABLE_NAME + " WHERE " + RssItem.COL_NAME_RSS_FEED + " = '" + this.id + "' AND " + RssItem.COL_NAME_CACHE + "='1'"; 		
		} else {
			query  = "SELECT " + RssItem.COL_NAME_ID + " FROM `" + RssItem.TABLE_NAME + " WHERE " + RssItem.COL_NAME_RSS_FEED + " = '" + this.id + "' AND " + RssItem.COL_NAME_CACHE + "!='1'"; 		
		}

		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return null;
			}
		}

		statement = this.conn.createStatement();
		result = statement.executeQuery(query);
		while (result.next()) {
			itemId = result.getInt(RssItem.COL_NAME_ID);
			item = new RssItem(itemId);
			list.add(item);
		}
		return list;
	}
	
	public boolean doesBlongsTo(int user) {
		return (user == this.user);
	}
	
	public boolean getFeeds() throws ParserConfigurationException, IOException, SAXException, JSONException {
		Document ret = null;
        DocumentBuilderFactory domFactory;
        DocumentBuilder builder;
		URL url = new URL(this.url);
		InputStream in = url.openStream();
		int i;
		Node item;
		
		domFactory = DocumentBuilderFactory.newInstance();
        builder = domFactory.newDocumentBuilder();
        
        Document doc = builder.parse(in);

        NodeList items = doc.getElementsByTagName("item");
        for (i = 0; i < items.getLength(); i++) {
        	item = items.item(i);
        	if (item != null && item.getNodeType() == Node.ELEMENT_NODE) {
        		String val;
        		Element element = (Element)item;
        		JSONObject data = new JSONObject();
        		val = getValue("title", element);
        		data.put("title", val);
        		data.put("link", getValue("link", element));
        		data.put("description", getValue("description", element));
        		if (getValue("author", element) != null) {
        			data.put("author", getValue("author", element));
        		}
        		if (getValue("content", element) != null) {
        			data.put("content", getValue("content", element));
        		} else {
        			data.put("content", getValue("description", element));        			
        		}
        		if (getValue("guid", element) != null) {
        			data.put("guid", getValue("guid", element));
        		}
        		
        		try {
            		RssItem itm = new RssItem(this.id, 0, mysql_real_escape_string(this.conn, data.toString()));
            		if (itm.isNew()) {
    					itm.create();
            		}
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
        	}
        }
		return true;
	}
	
	private static String getValue(String tag, Element element) {
		NodeList nodes = element.getElementsByTagName(tag);
		if (nodes.getLength() == 0)
			return null;
		Node node = (Node) nodes.item(0);
		return node.getTextContent();
	}
	
    private static String mysql_real_escape_string(java.sql.Connection link, String str) throws Exception
      {
          if (str == null) {
              return null;
          }
                                      
          if (str.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/? ]","").length() < 1) {
              return str;
          }
              
          String clean_string = str;
          clean_string = clean_string.replaceAll("\\\\", "\\\\\\\\");
          clean_string = clean_string.replaceAll("\\n","\\\\n");
          clean_string = clean_string.replaceAll("\\r", "\\\\r");
          clean_string = clean_string.replaceAll("\\t", "\\\\t");
          clean_string = clean_string.replaceAll("\\00", "\\\\0");
          clean_string = clean_string.replaceAll("'", "\\\\'");
          clean_string = clean_string.replaceAll("\\\"", "\\\\\"");
                                                              
          if (clean_string.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\\Q[\\E\\Q]\\E<>{}\\/?\\\\\"' ]"
            ,"").length() < 1) 
          {
              return clean_string;
          }
                              
          java.sql.Statement stmt = link.createStatement();
          String qry = "SELECT QUOTE('"+clean_string+"')";
              
          stmt.executeQuery(qry);
          java.sql.ResultSet resultSet = stmt.getResultSet();
          resultSet.first();
          String r = resultSet.getString(1);
          return r.substring(1,r.length() - 1);       
      }
}
